CREATE PROCEDURE [dbo].[ConvertDocumentNodeToHierarchy]
   @RootHierarchyKey uniqueidentifier,
   @ParentHierarchyKey uniqueidentifier,
   @DocumentNodeKey uniqueidentifier = null,
   @DocumentVersionKey uniqueidentifier = null,
   @Depth int = 0 AS
BEGIN
   DECLARE @ChildDocumentNodeKey uniqueidentifier,
           @ChildDocumentVersionKey uniqueidentifier,
           @ChildName nvarchar(50),
           @PriorChildDocumentVersionKey uniqueidentifier,
           @PriorChildName nvarchar(50),
           @NewHierarchyKey uniqueidentifier,
           @NewSort int
   SET NOCOUNT ON
   SET @PriorChildDocumentVersionKey = NULL
   SET @PriorChildName = NULL

   IF @DocumentNodeKey IS NOT NULL
   BEGIN
    DECLARE @DocumentName nvarchar(100),
            @DocumentTypeCode nvarchar(3),
            @hierarchyKey uniqueidentifier
    SET @hierarchyKey = NULL
    SELECT @DocumentName = DocumentName, @DocumentTypeCode = DocumentTypeCode from DocumentMain WHERE DocumentVersionKey = @DocumentVersionKey

      -- if this is a folder, figure out if it already exists (another document with the same parent and name)
    IF @DocumentTypeCode = 'FOL'
    BEGIN
        SELECT TOP 1 @hierarchyKey = HierarchyKey
        FROM [DocumentMain] a
        INNER JOIN [Hierarchy] b
            ON a.DocumentVersionKey = b.UniformKey
        WHERE a.DocumentTypeCode = 'FOL'
        AND a.DocumentName = @DocumentName
        AND b.ParentHierarchyKey = @ParentHierarchyKey
    END
    
    IF @hierarchyKey IS NOT NULL
    BEGIN
        -- Copy all the children below this into the existing folder and delete this copy from DocumentMain.
        SET @NewHierarchyKey = @hierarchyKey
        -- Delete later, to be safe
        INSERT INTO DuplicateDocuments VALUES (@DocumentVersionKey)
    END
    ELSE
    BEGIN
      -- figure out the sort key of the new hierarchy record
      EXEC asi_HierarchyGetAdjacentSortOut @ParentHierarchyKey, 3, @NewSort OUTPUT

      -- if the depth is not sent, get it from the parent + 1
      IF @Depth = 0
        SELECT @Depth = Depth + 1
          FROM Hierarchy
         WHERE HierarchyKey = @ParentHierarchyKey

      -- set the new hierarchy key for this record to match the DocumentNodeKey
      SET @NewHierarchyKey = @DocumentNodeKey

      -- create a hierarchy record for the document being sent IN
      IF NOT EXISTS (SELECT 1 FROM Hierarchy WHERE HierarchyKey = @NewHierarchyKey)
          INSERT INTO Hierarchy (HierarchyKey, RootHierarchyKey, ParentHierarchyKey, SortOrder, Depth, UniformType, UniformKey, IsChildAMemberOfParent)
          VALUES (@NewHierarchyKey, @RootHierarchyKey, @ParentHierarchyKey, @NewSort, @Depth, 'DocumentVersion', @DocumentVersionKey ,0)
    END
   END
   ELSE
      SET @NewHierarchyKey = @RootHierarchyKey

   SET @Depth = @Depth + 1

   -- get the next child of this record
   WHILE 1 = 1
   BEGIN
      SELECT TOP 1 @ChildDocumentNodeKey = a.DocumentNodeKey, @ChildDocumentVersionKey = b.DocumentVersionKey, @ChildName = b.DocumentName
        FROM DocumentNodeBackup a INNER JOIN DocumentMain b ON a.DocumentKey = b.DocumentKey
       WHERE (a.ParentDocumentNodeKey = @DocumentNodeKey
          OR (a.ParentDocumentNodeKey IS NULL AND @DocumentNodeKey IS NULL))
         AND (b.DocumentName > @PriorChildName
          OR @PriorChildName IS NULL
          OR (b.DocumentName = @PriorChildName AND b.DocumentVersionKey > @PriorChildDocumentVersionKey))
         AND (b.DocumentVersionKey <> @PriorChildDocumentVersionKey
          OR @PriorChildDocumentVersionKey IS NULL)
         AND a.DocumentRootKey = @RootHierarchyKey
       ORDER By b.DocumentName, b.DocumentKey
       IF @@ROWCOUNT = 0
          BREAK

       EXEC ConvertDocumentNodeToHierarchy @RootHierarchyKey, @NewHierarchyKey, @ChildDocumentNodeKey, @ChildDocumentVersionKey, @Depth

       SET @PriorChildDocumentVersionKey = @ChildDocumentVersionKey
       SET @PriorChildName = @ChildName
   END
   SET NOCOUNT OFF
END

GO